package com.hl.dao;

import com.hl.pojo.PanghuCommunity;
import com.hl.util.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class CommunityDaoImpl implements CommunityDao{
    QueryRunner queryRunner = new QueryRunner(JdbcUtil.dataSource);
    @Override
    public List<PanghuCommunity> search(String name, String startTime, String endTime, String pageNum, String pageSize) {
        List<PanghuCommunity> list = null;
        String sql = null;
        if (name!=""&&startTime!=""&&endTime!=""){
            sql = "select community_id as communityId," +
                    "community_name  as communityName," +
                    "community_address  as communityAddress," +
                    "create_time  as createTime," +
                    "community_province  as communityProvince," +
                    "community_city  as communityCity," +
                    "community_district  as communityDistrict," +
                    "community_area_code as communityAreaCode," +
                    "community_latitude as communityLatitude," +
                    "community_longitude as communityLongitude," +
                    "community_status as communityStatus," +
                    "community_coordinates as communityCoordinates" +
                    " from panghu_community where community_name like ? and create_time  between ? and ?";
        }else{
            sql = "select community_id as communityId," +
                    "community_name  as communityName," +
                    "community_address  as communityAddress," +
                    "create_time  as createTime," +
                    "community_province  as communityProvince," +
                    "community_city  as communityCity," +
                    "community_district  as communityDistrict," +
                    "community_area_code as communityAreaCode," +
                    "community_latitude as communityLatitude," +
                    "community_longitude as communityLongitude," +
                    "community_status as communityStatus," +
                    "community_coordinates as communityCoordinates" +
                    " from panghu_community";
        }
        try {
            if (name!=""&&startTime!=""&&endTime!=""){
                list = queryRunner.query(sql,new BeanListHandler<>(PanghuCommunity.class),"%"+name+"%",startTime,endTime);
            }else {
                list = queryRunner.query(sql,new BeanListHandler<>(PanghuCommunity.class));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //新增小区
    @Override
    public int save(PanghuCommunity community) {
        int num = 0;
//        String sql = "insert into panghu_community(" +
//                "community_name as communityName,community_address as communityAddress," +
//                "community_province as communityProvince,community_city as communityCity," +
//                "community_district as communityDistrict,community_area_code as communityAreaCode," +
//                "community_latitude as communityLatitude,community_longitude as communityLongitude," +
//                "community_status as communityStatus,community_coordinates as communityCoordinates) values (?,?,?,?,?,?,?,?,?,?)";
        String sql = "insert into panghu_community(" +
                "community_name,community_address," +
                "community_province,community_city," +
                "community_district,community_area_code," +
                "community_latitude,community_longitude," +
                "community_status,community_coordinates) values (?,?,?,?,?,?,?,?,?,?)";
        try {
            num = queryRunner.update(
                    sql,community.getCommunityName(),
                    community.getCommunityAddress(),community.getCommunityProvince(),
                    community.getCommunityCity(),community.getCommunityDistrict(),
                    community.getCommunityAreaCode(),
                    community.getCommunityLatitude(),community.getCommunityLongitude(),
                    community.getCommunityStatus(),community.getCommunityCoordinates());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int update(PanghuCommunity community) {
        int num = 0;
        String sql = "update panghu_community set community_name = ?,community_address = ?,community_province = ?," +
                "community_city = ?,community_district = ?," +
                "community_area_code = ?,community_latitude = ?,community_longitude = ?,community_status = ?,community_coordinates = ? where community_id = ?";
        try {
            num = queryRunner.update(sql,community.getCommunityName(),
                    community.getCommunityAddress(),community.getCommunityProvince(),
                    community.getCommunityCity(),community.getCommunityDistrict(),
                    community.getCommunityAreaCode(),community.getCommunityLatitude(),
                    community.getCommunityLongitude(),community.getCommunityStatus(),
                    community.getCommunityCoordinates(),community.getCommunityId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public Integer findUser(int user) {
        int i = 0;
        String sql = "select count(wechat_user_unionid) from panghu_community join panghu_wechat_user_community on panghu_community.community_id = panghu_wechat_user_community.panghu_community_id where community_id = ?";
        try {
           Object obj = queryRunner.query(sql,new ScalarHandler<>(),user);
           if(obj!=null){
               String s = obj.toString();
               i = Integer.parseInt(s);
           }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public Integer findShop(int shop) {
        int i = 0;
        String sql = "select count(panghu_shop_id) from panghu_community join panghu_shop_community on panghu_community.community_id = panghu_shop_community.panghu_community_id where community_id = ?";
        try {
            Object obj = queryRunner.query(sql,new ScalarHandler<>(),shop);
            if(obj!=null){
                String s = obj.toString();
                i = Integer.parseInt(s);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }


}
